import pymysql


class DBUtil:
    # 定义变量
    conn = None

    # 创建连接conn
    @classmethod
    def __connect_db(cls):
        cls.conn = pymysql.Connect(host="211.103.136.244", port=7061, user="student",
                                   password="iHRM_student_2021", database="test_db", charset="utf8")
        return cls.conn

    # 关闭连接conn
    @classmethod
    def __close_db(cls):
        cls.conn.close()

    # 定义查询数据库的方法
    @classmethod
    def get_one(cls, sql):
        cursor = None
        resp = None

        try:
            # 创建连接conn
            cls.conn = cls.__connect_db()
            # 获取游标
            cursor = cls.conn.cursor()
            # 执行sql,获取结果，把结果返回
            cursor.execute(sql)
            resp = cursor.fetchone()
        except Exception as err:
            print("查询数据库错误的日志=", str(err))
        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接conn
            cls.__close_db()
            return resp

    # 定义增删改数据库的方法
    @classmethod
    def uid_db(cls, sql):
        cursor = None
        # 创建连接conn
        cls.conn = cls.__connect_db()
        # 获取游标
        cursor = cls.conn.cursor()
        # 执行sql,打印影响了数据库多少行
        cursor.execute(sql)
        #  事务的提交、
        cls.conn.commit()
        affected_rows = cls.conn.affected_rows()
        print("影响的行数：", affected_rows)
        # 回滚
        cls.conn.rollback()
        # 关闭游标
        cursor.close()
        # 关闭连接conn
        cls.conn.close()


if __name__ == '__main__':
    result_query=DBUtil.get_one("SELECT  * from t_book ;")
    print("result_query=",result_query)
    updata_sql="UPDATE t_book SET title ='李白吃不下了' WHERE id = 100;"
    result_updata=DBUtil.uid_db(updata_sql)